package com.example.wheresmystuff2;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

/**
 * This class creates a database with 2 tables: one for User Info and one for Item Info.
 * The tables would store data created on the Sign Up Page and the Add Item Page.
 * The values stored can be deleted, modified, and returned as an array.
 * @author Patrick Yeung
 *
 */
public class Database {
	
	private SQLiteDatabase db; //references the database manager class
	private DatabaseHelper dbHelper;
	
	//Database table statements for onCreate
	private String[] allUserColumns = { DatabaseHelper.COL_USER_ID, DatabaseHelper.COL_USERNAME, DatabaseHelper.COL_PASSWORD, DatabaseHelper.COL_EMAIL, DatabaseHelper.COL_COUNT, DatabaseHelper.COL_LOCKED, DatabaseHelper.COL_ADMIN };
	private String[] allItemColumns = { DatabaseHelper.COL_ITEM_ID, DatabaseHelper.COL_ITEM, DatabaseHelper.COL_DESCRIPTION, DatabaseHelper.COL_LOCATION, DatabaseHelper.COL_STATUS, DatabaseHelper.COL_CATEGORY, DatabaseHelper.COL_TYPE, DatabaseHelper.COL_DATE, DatabaseHelper.COL_REWARD };
	
	/**
	 * This method is a constructor for database that allows for it to be accessed from other activities
	 * @param context The activity that it is being opened in
	 */
	public Database(Context context)	{
		dbHelper = new DatabaseHelper(context);
	}
	
	/**
	 * This method allows us to open the database for writing
	 * @throws SQLException Exception just in case
	 */
	public void open() throws SQLException	{
		db = dbHelper.getWritableDatabase();
	}
	
	/**
	 * This method closes the database when everything is done writing to it
	 */
  	public void close() {
  		dbHelper.close();
  	}
	
	/**
	 * This method adds to the user table when a new user is created in the SignIn activity
	 * @param username The user's username
	 * @param password The user's password
	 * @param email The user's email
	 * @param count The user's login attempt count, defaults to 3
	 * @param locked Uses default of false to state the all accounts are current not locked.
	 * @param admin Uses a boolean to state if a user is an admin or not
	 */
	public void addUserRow(String username, String password, String email, int count, int locked, int admin)	{
		ContentValues values = new ContentValues();
		values.put(DatabaseHelper.COL_USERNAME, username);
		values.put(DatabaseHelper.COL_PASSWORD, password);
		values.put(DatabaseHelper.COL_EMAIL, email);
		values.put(DatabaseHelper.COL_COUNT, count);
		values.put(DatabaseHelper.COL_LOCKED, locked);
		values.put(DatabaseHelper.COL_ADMIN, admin);
		
		db.insert(DatabaseHelper.USER_TABLE_NAME, null, values);
		
	}
	 /**
	  * This method deletes a row from the user table upon request
	  * @param username Takes in a username to check for, to know what row to delete
	  */
	public void deleteUserRow(String username)	{
		db.delete(DatabaseHelper.USER_TABLE_NAME, DatabaseHelper.COL_USERNAME + " = '" + username + "'", null);
	}
	
	/**
	 * This method updates the user with specified parameters
	 * @param username The new username
	 * @param password The new password
	 * @param email The new email
	 * @param count The counter for login in attempt
	 * @param locked The boolean for if user is locked out or not
	 * @param admin The boolean for if user is an admin or not
	 */
	public void updateUserRow(String username, String password, String email, int count, int locked, int admin)	{
		ContentValues values = new ContentValues();
		values.put(DatabaseHelper.COL_USERNAME, username);
		values.put(DatabaseHelper.COL_PASSWORD, password);
		values.put(DatabaseHelper.COL_EMAIL, email);
		values.put(DatabaseHelper.COL_COUNT, count);
		values.put(DatabaseHelper.COL_LOCKED, locked);
		values.put(DatabaseHelper.COL_ADMIN, admin);
		
		Log.d(DatabaseHelper.class.getName(), values.toString());
		db.update(DatabaseHelper.USER_TABLE_NAME, values, DatabaseHelper.COL_USERNAME + " = '" + username + "'", null);
	}
	/**
	 * This method looks for a username in the database and returns the info in that user's row
	 * @param username Takes in a username to search for
	 * @return Returns the info row for that user
	 */
	public ArrayList<Object> getUserRowAsArray(String username)
	{
		ArrayList<Object> rowArray = new ArrayList<Object>();
		Cursor cursor = db.rawQuery("SELECT * FROM " + DatabaseHelper.USER_TABLE_NAME + " WHERE " + DatabaseHelper.COL_USERNAME + " = '" + username + "'", null);
			
		cursor.moveToFirst();
		rowArray.add(cursor.getString(1));
		rowArray.add(cursor.getString(2));
		rowArray.add(cursor.getString(3));
		rowArray.add(cursor.getInt(4));
		rowArray.add(cursor.getInt(5));
		rowArray.add(cursor.getInt(6));
		cursor.close();
		return rowArray;
	}
	
/**
 * This method is not needed but may be of use later. I found no need to remove it
 * It returns the entire database as an array list of array lists
 */
	public ArrayList<ArrayList<Object>> getUserTableAsArray(){
		ArrayList<ArrayList<Object>> dataArray = new ArrayList<ArrayList<Object>>();
		Cursor cursor = db.query(DatabaseHelper.USER_TABLE_NAME, allUserColumns, 
				null, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast())	{
			ArrayList<Object> dataList = new ArrayList<Object>();
			dataList.add(cursor.getString(1));
			dataList.add(cursor.getString(2));
			dataList.add(cursor.getString(3));
			dataList.add(cursor.getInt(4));
			dataList.add(cursor.getInt(5));
			dataList.add(cursor.getInt(6));		
			dataArray.add(dataList);
			cursor.moveToNext();
		}
		cursor.close();
		return dataArray;
	}
	
	/**
	 * This method adds an item to the database's item table
	 * @param name Item's name
	 * @param description Item's description
	 * @param location Item's location
	 * @param status Item's status (open or closed)
	 * @param category Item's category (heirloom, keepsake, etc)
	 * @param type Item's type (lost, found, donated, requested)
	 * @param date Date in which item was added
	 * @param reward Reward for return of item
	 */
	public void addItemRow(String name, String description, String location, String status, String category, String type, String date, String reward)	{
		ContentValues values = new ContentValues();
		values.put(DatabaseHelper.COL_ITEM, name);
		values.put(DatabaseHelper.COL_DESCRIPTION, description);
		values.put(DatabaseHelper.COL_LOCATION, location);
		values.put(DatabaseHelper.COL_STATUS, status);
		values.put(DatabaseHelper.COL_CATEGORY, category);
		values.put(DatabaseHelper.COL_TYPE, type);
		values.put(DatabaseHelper.COL_DATE, date);
		values.put(DatabaseHelper.COL_REWARD, reward);

		db.insert(DatabaseHelper.ITEM_TABLE_NAME, null, values);
	}
	 /**
	  * This method takes in an item and searches for it in the database
	  * It will delete the information on the item if returned.
	  * @param item Takes in the item to remove.
	  */
	public void deleteItemRow(String name)	{
		db.delete(DatabaseHelper.ITEM_TABLE_NAME, DatabaseHelper.COL_ITEM + "='" + name +"'", null);
	}
	
	/**
	 * This method allows you to edit the item with new information
	 * @param name Item's name
	 * @param description Item's description
	 * @param location Item's location
	 * @param status Item's status (open or closed)
	 * @param category Item's category (heirloom, keepsake, etc)
	 * @param type Item's type (lost, found, donated, requested)
	 * @param date Date in which item was added
	 * @param reward Reward for return of item
	 */
	public void updateItemRow(String name, String description, String location, String status, String category, String type, String date, String reward)	{
		ContentValues values = new ContentValues();
		values.put(DatabaseHelper.COL_ITEM, name);
		values.put(DatabaseHelper.COL_DESCRIPTION, description);
		values.put(DatabaseHelper.COL_LOCATION, location);
		values.put(DatabaseHelper.COL_STATUS, status);
		values.put(DatabaseHelper.COL_CATEGORY, category);
		values.put(DatabaseHelper.COL_TYPE, type);
		values.put(DatabaseHelper.COL_DATE, date);
		values.put(DatabaseHelper.COL_REWARD, reward);

		db.update(DatabaseHelper.ITEM_TABLE_NAME, values, DatabaseHelper.COL_ITEM + " = '" + name +"'", null);
	}
/**
 * This method takes in an item name and searches the database for that name. 
 * It then returns the row of the information for the found item	
 * @param name Takes in the name of the item
 * @return Returns the array list of the item's information
 */
	public ArrayList<Object> getItemRowAsArray(String name)	{
		ArrayList<Object> rowArray = new ArrayList<Object>();
		Cursor cursor = db.rawQuery("SELECT * FROM " + DatabaseHelper.ITEM_TABLE_NAME + " WHERE " + DatabaseHelper.COL_ITEM + " = '" + name + "'", null);	
		
		cursor.moveToFirst();
		rowArray.add(cursor.getString(1));
		rowArray.add(cursor.getString(2));
		rowArray.add(cursor.getString(3));
		rowArray.add(cursor.getString(4));
		rowArray.add(cursor.getString(5));
		rowArray.add(cursor.getString(6));
		rowArray.add(cursor.getString(7));
		rowArray.add(cursor.getString(8));
		cursor.close();
		return rowArray;
	}
/**
 *As of the moment, this method is useless, but may be of use later. 
 *This method returns the entire table of items.
 */
	public ArrayList<ArrayList<Object>> getItemTableAsArray()	{
		ArrayList<ArrayList<Object>> dataArray = new ArrayList<ArrayList<Object>>();
		Cursor cursor = db.query(DatabaseHelper.ITEM_TABLE_NAME, allItemColumns, 
				null, null, null, null, null);
 
		cursor.moveToFirst();
		while(!cursor.isAfterLast())	{
			ArrayList<Object> dataList = new ArrayList<Object>();

			dataList.add(cursor.getString(1));
			dataList.add(cursor.getString(2));
			dataList.add(cursor.getString(3));
			dataList.add(cursor.getString(4));
			dataList.add(cursor.getString(5));
			dataList.add(cursor.getString(6));
			dataList.add(cursor.getString(7));
			dataList.add(cursor.getString(8));

			dataArray.add(dataList);
			cursor.moveToNext();
		}
		cursor.close();
		return dataArray;
	}
	
	public Map<String, ArrayList<Object>> getItemTableAsHashMap()	{
		Map<String,ArrayList<Object>> dataHash = new HashMap<String, ArrayList<Object>>();
		Cursor cursor = db.query(DatabaseHelper.ITEM_TABLE_NAME, allItemColumns, 
				null, null, null, null, null);
 
		cursor.moveToFirst();
		while(!cursor.isAfterLast())	{
			ArrayList<Object> dataList = new ArrayList<Object>();
			
			dataList.add(cursor.getString(2));
			dataList.add(cursor.getString(3));
			dataList.add(cursor.getString(4));
			dataList.add(cursor.getString(5));
			dataList.add(cursor.getString(6));
			dataList.add(cursor.getString(7));
			dataList.add(cursor.getString(8));

			dataHash.put(cursor.getString(1),dataList);
			cursor.moveToNext();
		}
		cursor.close();
		return dataHash;
	}
}
